﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections;

namespace MixScript.DBService
{
    public class cDBHelper : cDBService
    {
        public cDBHelper()
        {

        }

        public bool genidStepDown(string IDName)
        {
            try
            {
                string sSql = "update sy_genid set icurr=icurr-istep where genid='" + IDName + "'";
                if (Dbserver.openConn())
                {
                    Dbserver.ExecuteNonQuery(sSql);
                    return true;
                }
                else
                {
                    throw new Exception("IDName Could not be found");
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        public bool genidStepUP(string idtype, string IDName)
        {
            try
            {
                DateTime dd = DateTime.Now;
                string sSql = "";
                if (idtype == "D")
                {
                     sSql = "update sy_genid set icurr=icurr+istep where genid='" + IDName + "' and syear='" + dd.ToString("yyyy") + "' and smonth='" + dd.ToString("MM") + "' and sday='" + dd.ToString("dd") + "'";
                }
                else if (idtype == "M")
                {
                     sSql = "update sy_genid set icurr=icurr+istep where genid='" + IDName + "' and syear='" + dd.ToString("yyyy") + "' and smonth='" + dd.ToString("MM") + "' ";
                }
                if (Dbserver.openConn())
                {
                    Dbserver.ExecuteNonQuery(sSql);
                    Dbserver.CommitAndCloseConn();
                    return true;
                }
                else
                {
                    throw new Exception("IDName Could not be found");
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        public bool genidStepUP(string IDName)
        {
            try
            {
                string sSql = "update sy_genid set icurr=icurr+istep where genid='" + IDName + "'";
                if (Dbserver.openConn())
                {
                    Dbserver.ExecuteNonQuery(sSql);
                    Dbserver.CommitAndCloseConn();
                    return true;
                }
                else
                {
                    throw new Exception("IDName Could not be found");
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

        public string genIDCommit(string IDName)
        {
            string sid = genId(IDName);
            genidStepUP(IDName);
            return sid;
        }


        public string genIDCommit(string idtype, string IDName)
        {
            string sid = genId(idtype, IDName);
            genidStepUP(idtype, IDName);
            return sid;
        }


        public string genId(string idtype, string IDName)
        {
            try
            {
                DataTable dt = new DataTable();

                DateTime dd;
                DateTime.TryParse(Dbserver.ExecuteScalar("select getdate()").ToString(), out dd);
                string sql = "select * from sy_genid where genid='" + IDName + "'";
                dt = Dbserver.ExecuteQuery(sql, "genid");
                if(dt.Rows.Count>0)
                {
                    if (idtype == "D")
                    {
                        sql = "select * from sy_genid where genid='" + IDName + "' and syear='" + dd.ToString("yyyy") + "' and smonth='" + dd.ToString("MM") + "' and sday='" + dd.ToString("dd") + "'";
                    }
                    else if (idtype == "M")
                    {
                        sql = "select * from sy_genid where genid='" + IDName + "' and syear='" + dd.ToString("yyyy") + "' and smonth='" + dd.ToString("MM") + "' ";
                    }
                     int imin, imax,  istep = 0;
                     imin = (int)dt.Rows[0]["imin"];
                     imax = (int)dt.Rows[0]["imax"];
                     istep = (int)dt.Rows[0]["istep"];
                     string pref, suf = "";
                     pref = dt.Rows[0]["prefix"].ToString();
                     suf = dt.Rows[0]["sufix"].ToString() ;


                    dt = new DataTable();
                    dt = Dbserver.ExecuteQuery(sql, "genid");
                    if (dt.Rows.Count == 0)
                    {
                        dt = new DataTable();
                        getTableStructure(ref dt, "sy_genid");
                        DataRow dr = dt.NewRow();
                        dr["genid"] = IDName;
                        dr["syear"] = dd.ToString("yyyy");
                        dr["smonth"] = dd.ToString("MM");
                        dr["sday"] = dd.ToString("dd");
                        dr["imin"] = imin;
                        dr["imax"] = imax;
                        dr["idtyp"] = idtype;
                        dr["istep"] = istep;
                        dr["icurr"] = imin;
                        dt.Rows.Add(dr);
                        string ss="";
                        if (Dbserver.openConn())
                        {
                            Dbserver.SqlUpdate(dt, "sy_genid", out ss);
                            Dbserver.CommitAndCloseConn();
                        }
                        
                       int l = imax.ToString().Length;
                       return pref.Trim() + (imin+istep).ToString().PadLeft(l, '0') + suf.ToString();

                    }
                    else
                    {
                        Int32 icurr = (Int32)dt.Rows[0]["icurr"];
                        Int32 imin1 = (Int32)dt.Rows[0]["imin"];
                        Int64 imax1 = 0;// (Int64)dt.Rows[0]["imax"].ToString();
                        Int64.TryParse(dt.Rows[0]["imax"].ToString(), out imax1);
                        int istep1 = (int)dt.Rows[0]["istep"];
                        string prefix = dt.Rows[0]["prefix"].ToString();
                        string sufix = dt.Rows[0]["sufix"].ToString();
                        string smax = imax.ToString();
                        int l = smax.Length;
                        return prefix.Trim() + (icurr + istep).ToString().PadLeft(l, '0') + sufix.ToString();

                    }


                    
                  
                }
                else
                {
                    throw new Exception("IDName Could not be found");
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public string genId(string IDName)
        {
            try
            {
                DataTable dt = new DataTable();
                string sql = "select * from sy_genid where genid='" + IDName + "'";
                dt = Dbserver.ExecuteQuery(sql, "genid");
                if (dt.Rows.Count > 0)
                {

                    Int32 icurr = (Int32)dt.Rows[0]["icurr"];
                    Int32 imin = (Int32)dt.Rows[0]["imin"];
                    Int64 imax =0;// (Int64)dt.Rows[0]["imax"].ToString();
                    Int64.TryParse(dt.Rows[0]["imax"].ToString(), out imax);
                    int istep = (int)dt.Rows[0]["istep"];
                    string prefix = dt.Rows[0]["prefix"].ToString();
                    string sufix = dt.Rows[0]["sufix"].ToString();
                    string smax = imax.ToString();
                    int l = smax.Length;
                    return prefix.Trim() + (icurr + istep).ToString().PadLeft(l, '0') + sufix.ToString();

                }
                else
                {
                    throw new Exception("IDName Could not be found");
                }


            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
        }

    }
}